import re
import pymssql

ms = pymssql.connect(host="120.76.145.236",user="sa",password="123456..zxcvbnm",database="WAEMS",charset="UTF8")
inserSql = "insert into address(addressName,addressParent) VALUES (%s,%d);SELECT @@IDENTITY"
selectSql = "select addressId from Address where [addressName] = %s"

cur = ms.cursor()
if not cur:
    raise (NameError,"链接数据库失败")
else:
    print("yes")


f = open('site.html','r')
html = f.read()
f.close()
list = re.findall('<tbody>(.*?)</tbody>',html,re.S)

for i in range(1,2,1):
    print("-------------")
    l1 = re.findall('<tr bgcolor="#f8f8f8">(.*?)</tr>', list[i], re.S)
    #f8f8f8
    for j in range(0,l1.__len__(),1):
        print("+++++++++++")
        #获取到区县名称
        l2 = re.search('class="blue">(.*?)</a></strong></td>', l1[j], re.S).group(1)
        #输出名称
        #print(l2)
        cur.execute(selectSql, l2)
        resList = cur.fetchone()[0]
        pid = int(resList)
        #print(pid)

        #获取该区县下的街道合集
        l2_city = re.findall('class="blue">(.*?)</a>',l1[j],re.S)
        for k in range(1, l2_city.__len__(), 1):
            #输出该乡镇街道名称
            #print(l2_city[k])
            cur.execute(inserSql, (l2_city[k],pid))
            ms.commit()


